﻿#region 
using System;
using System.Data;
using System.Data.OracleClient;
using System.Configuration;
using System.Collections;
using System.Xml;
using System.IO;
using System.Text;
//using Oracle.DataAccess.Client;
using System.Security.Cryptography;
#endregion

namespace PUBCOM
{
    public class PUBDB
    {
        #region AppSettings
        public string pubGetAppSettingsConn()
        {
            return pubGetAppSettings("YT_SPS_CONN");
        }
        #region 综合业务数据库
        /// <summary>
        ///综合业务数据库连接字符串
        /// </summary>
        public static string ConnectionString_DSN_YTBZ
        {
            get
            {
                string M_ServerIP = ConfigurationManager.AppSettings["SQLServerIP_YTBZ"];
                string M_DATABASE = ConfigurationManager.AppSettings["DataBase_YTBZ"];
                string M_USER = ConfigurationManager.AppSettings["LoginUser_YTBZ"];
                string M_PWD = AddSec(ConfigurationManager.AppSettings["LoginPassword_YTBZ"]);
                string DSN_YTBZ = "user id=" + M_USER + ";password=" + M_PWD + ";Enlist=false;initial catalog=" + M_DATABASE + ";data source=" + M_ServerIP + ";Max Pool Size=500";

                string _connectionString = DSN_YTBZ;

                return _connectionString;
            }
        }
        #region 密码加密函数
        public static string AddSec(string M_Info)
        {
            int Num = M_Info.Length;
            int Allnum = 0;
            int Chg;
            int Other;
            string Pass = "";
            string OtherPass = "";
            if (Num != 0)
            {
                for (int i = 0; i < Num; i++)
                {
                    Allnum += (int)Convert.ToChar(M_Info.Substring(Num - i - 1, 1).ToString());
                }
                Allnum = Allnum * 2;
                for (int i = 0; i < Num; i++)
                {
                    Chg = Allnum % 26;
                    Allnum = Allnum - (int)Convert.ToChar(M_Info.Substring(Num - i - 1, 1).ToString());
                    Pass += Convert.ToChar((int)'A' + Chg);
                }
                Other = 12 - Num;
                Allnum = Allnum * 3;
                for (int i = 0; i < Other; i++)
                {
                    Chg = Allnum % 26;
                    Allnum -= Chg * 3;
                    OtherPass += Convert.ToChar((int)'A' + Chg);
                }
                return OtherPass + Pass;

            }
            else
            {
                return "";
            }
        }
        #endregion
        #endregion
        public string pubGetAppSettings(string strProIN)
        {
            return ConfigurationManager.AppSettings[strProIN].ToString();
        }
        #endregion

        #region 数据库操作

        #region dt opt
        public DataSet proGetDS(SortedList SLData, string spName)
        {
            DataSet ds = HelperQuery(spName, SLData);
            return ds;
        }
        public DataTable proGetDT(SortedList SLData, string spName)
        {
            DataSet ds = proGetDS(SLData, spName);
            if (ds != null && ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            else
            {
                return null;
            }
        }
        public string proGetDTOne(SortedList SLData, string spName)
        {
            DataSet ds = proGetDS(SLData, spName);
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                return ds.Tables[0].Rows[0][0].ToString();
            }
            else
            {
                return "";
            }
        }
        public string proSetDT(SortedList SLData, string spName)
        {
            object objBack = HelperExec(spName, SLData);
            return objBack == null ? "" : objBack.ToString();
        }
        #endregion

        #region 执行
        private object HelperExec(string cmdText, SortedList SLData)
        {
            string connStr = pubGetAppSettingsConn();
            OracleConnection conn = null;
            object objBack = null;
            try
            {
                #region cmdExec
                OracleCommand cmd = new OracleCommand();
                conn = new OracleConnection(connStr);
                conn.Open();
                if (SLData == null)
                {
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = cmdText;
                    cmd.ExecuteNonQuery();
                }
                else
                {
                    OracleParameter[] commandParameters = getParame(conn, cmdText);
                    setParame(SLData, ref commandParameters);

                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = cmdText;
                    if (commandParameters != null)
                    {
                        foreach (OracleParameter parm in commandParameters)
                        {
                            cmd.Parameters.Add(parm);
                        }
                    }
                    cmd.ExecuteNonQuery();
                    for (int i = 0; i < commandParameters.Length; i++)
                    {
                        if (commandParameters[i].Direction == ParameterDirection.Output
                            || commandParameters[i].Direction == ParameterDirection.InputOutput)
                        {
                            objBack = commandParameters[i].Value.ToString().Split('$')[0];
                            //txtSaveErro(cmdText+ objBack.ToString()+ commandParameters[i].Size);
                        }
                    }
                }
                return objBack;
                #endregion
            }
            catch (Exception ex)
            {
                txtSaveErro(" 存储过程 " + cmdText + " 参数 " + SDListToStr(SLData) + " 错误 " + ex.ToString());
                throw ex;
            }
            finally
            {
                #region connClose
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    try
                    {
                        conn.Close();
                    }
                    catch
                    { }
                }
                #endregion
            }
        }
        #endregion

        #region 查询
        private DataSet HelperQuery(string cmdText, SortedList SLData)
        {
            string connStr = pubGetAppSettingsConn();
            OracleConnection conn = null;
            DataSet dsBack = new DataSet();
            try
            {
                #region cmdExec
                OracleCommand cmd = new OracleCommand();
                conn = new OracleConnection(connStr);
                conn.Open();
                if (SLData == null)
                {
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = cmdText;
                    cmd.ExecuteNonQuery();
                    OracleDataAdapter da = new OracleDataAdapter(cmd);
                    da.TableMappings.Add("JIEYUANTABLE", "JIEYUANTABLE");
                    da.Fill(dsBack);
                }
                else
                {
                    OracleParameter[] commandParameters = getParame(conn, cmdText);
                    setParame(SLData, ref commandParameters);

                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = cmdText;
                    if (commandParameters != null)
                    {
                        foreach (OracleParameter parm in commandParameters)
                        {
                            cmd.Parameters.Add(parm);
                        }
                    }
                    OracleDataAdapter da = new OracleDataAdapter(cmd);
                    da.TableMappings.Add("JIEYUANTABLE", "JIEYUANTABLE");
                    da.Fill(dsBack);
                }
                return dsBack;
                #endregion
            }
            catch (Exception ex)
            {
                txtSaveErro(" 存储过程 " + cmdText + " 参数 " + SDListToStr(SLData) + " 错误 " + ex.ToString());
                throw ex;
            }
            finally
            {
                #region connClose
                if (conn != null && conn.State != ConnectionState.Closed)
                {
                    try
                    {
                        conn.Close();
                    }
                    catch
                    { }
                }
                #endregion
            }
        }
        #endregion

        #region 参数
        private Hashtable paramCache = Hashtable.Synchronized(new Hashtable());
        public void ClearCash()
        {
            paramCache.Clear();
        }
        private OracleParameter[] getParame(OracleConnection connection, string spName)
        {
            //SELECT PACKAGE_NAME, OBJECT_NAME, IN_OUT, ARGUMENT_NAME, DATA_TYPE FROM USER_ARGUMENTS WHERE OBJECT_NAME LIKE '%USERPASSRESET%'
            OracleParameter[] cachedParameters;
            cachedParameters = paramCache[spName] as OracleParameter[];
            if (cachedParameters == null)
            {
                string[] strTT = spName.Split('.');
                string strSql = "SELECT IN_OUT, ARGUMENT_NAME, DATA_TYPE FROM USER_ARGUMENTS "
                              + " WHERE UPPER(PACKAGE_NAME)=UPPER('" + strTT[0] + "')"
                              + "   AND UPPER(OBJECT_NAME) =UPPER('" + strTT[1] + "')";
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = connection;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = strSql;
                cmd.ExecuteNonQuery();
                OracleDataAdapter da = new OracleDataAdapter(cmd);
                da.TableMappings.Add("JIEYUANTABLE", "JIEYUANTABLE");
                DataSet dsBack = new DataSet();
                da.Fill(dsBack);
                if (dsBack != null && dsBack.Tables.Count > 0 && dsBack.Tables[0].Rows.Count > 0)
                {
                    int prameCount = dsBack.Tables[0].Rows.Count;
                    OracleParameter[] discoveredParameters = new OracleParameter[prameCount];
                    for (int i = 0; i < prameCount; i++)
                    {
                        DataRow drPrame = dsBack.Tables[0].Rows[i];
                        //IN_OUT, ARGUMENT_NAME, DATA_TYPE;
                        //REF CURSOR  NUMBER  DATE VARCHAR2  
                        if (drPrame["DATA_TYPE"].ToString() == "REF CURSOR")
                        {
                            discoveredParameters[i] = new OracleParameter(drPrame["ARGUMENT_NAME"].ToString(), OracleType.Cursor);
                        }
                        if (drPrame["DATA_TYPE"].ToString() == "NUMBER")
                        {
                            discoveredParameters[i] = new OracleParameter(drPrame["ARGUMENT_NAME"].ToString(), OracleType.Number);
                        }
                        if (drPrame["DATA_TYPE"].ToString() == "DATE")
                        {
                            discoveredParameters[i] = new OracleParameter(drPrame["ARGUMENT_NAME"].ToString(), OracleType.DateTime);
                        }
                        if (drPrame["DATA_TYPE"].ToString() == "VARCHAR2")
                        {
                            discoveredParameters[i] = new OracleParameter(drPrame["ARGUMENT_NAME"].ToString(), OracleType.VarChar);
                        }
                        if (drPrame["IN_OUT"].ToString() == "OUT")
                        {
                            discoveredParameters[i].Direction = ParameterDirection.Output;
                            if (drPrame["DATA_TYPE"].ToString() == "VARCHAR2")
                            { discoveredParameters[i].Size = 1024; }
                        }

                    }
                    foreach (OracleParameter discoveredParameter in discoveredParameters)
                    {
                        discoveredParameter.Value = DBNull.Value;
                    }
                    paramCache[spName] = discoveredParameters;
                    cachedParameters = discoveredParameters;
                }
            }
            OracleParameter[] clonedParameters = new OracleParameter[cachedParameters.Length];
            for (int i = 0, j = cachedParameters.Length; i < j; i++)
            {
                clonedParameters[i] = (OracleParameter)((ICloneable)cachedParameters[i]).Clone();
            }
            return clonedParameters;
        }

        //win2012 不能使用
        private OracleParameter[] getParameOLD(OracleConnection connection, string spName)
        {
            string hashKey = connection.ConnectionString + ":" + spName;
            OracleParameter[] cachedParameters;
            cachedParameters = paramCache[hashKey] as OracleParameter[];
            //if (cachedParameters == null)
            {
                OracleCommand cmd = new OracleCommand(spName, connection);
                cmd.CommandType = CommandType.StoredProcedure;

                OracleCommandBuilder.DeriveParameters(cmd);
                OracleParameter[] discoveredParameters = new OracleParameter[cmd.Parameters.Count];
                cmd.Parameters.CopyTo(discoveredParameters, 0);
                foreach (OracleParameter discoveredParameter in discoveredParameters)
                {
                    discoveredParameter.Value = DBNull.Value;
                }
                paramCache[hashKey] = discoveredParameters;
                cachedParameters = discoveredParameters;
            }

            OracleParameter[] clonedParameters = new OracleParameter[cachedParameters.Length];
            for (int i = 0, j = cachedParameters.Length; i < j; i++)
            {
                clonedParameters[i] = (OracleParameter)((ICloneable)cachedParameters[i]).Clone();
            }
            return clonedParameters;
        }

        private void setParame(SortedList SLParamsTT, ref OracleParameter[] myParams)
        {
            SortedList SLParams = new SortedList();
            foreach (DictionaryEntry element in SLParamsTT)
            {
                SLParams[element.Key.ToString().ToUpper()] = element.Value;
            }

            for (int i = 0; i < myParams.Length; i++)
            {
                if (myParams[i].Direction == ParameterDirection.Output) { continue; }
                string paramName = myParams[i].ParameterName.ToUpper();//参数名
                object paramValue = SLParams[paramName];

                if (paramValue == null || paramValue.ToString() == "")
                {
                    if (paramName == "V_CURRPAGESIZE") { myParams[i].Value = 65000; }
                    if (paramName == "V_CURRPAGE") { myParams[i].Value = 0; }

                    continue;
                }

                OracleType dataType = myParams[i].OracleType;//数据类型
                if (dataType == OracleType.Int16 || dataType == OracleType.Int32)
                {
                    try { myParams[i].Value = Convert.ToInt32(paramValue); }
                    catch { throw new Exception("转换整数格式字段出错！"); }
                }
                else if (dataType == OracleType.Double || dataType == OracleType.Number || dataType == OracleType.Float)
                {
                    try { myParams[i].Value = Convert.ToDecimal(paramValue); }
                    catch { throw new Exception("转换浮点数格式字段出错！"); }
                }
                else if (dataType == OracleType.DateTime)
                {
                    try { myParams[i].Value = Convert.ToDateTime(paramValue); }
                    catch { throw new Exception("转换日期格式字段出错！"); }
                }
                else
                {
                    myParams[i].Value = paramValue.ToString().Trim();
                }
            }
        }
        #endregion

        #region SDListToStr
        public string SDListToStr(SortedList SDList)
        {
            if (SDList == null) { return ""; }
            string strBack = "";
            foreach (DictionaryEntry element in SDList) { strBack += element.Key.ToString().ToUpper() + ":" + element.Value + "$"; }
            return strBack;
        }
        #endregion


  

        public bool ModifyMasteAndDetail(DataTable dt, string storedProcName, DataTable dt2, string storedProcName2)
        {
            OracleTransaction oracletrans = null;
            bool result = true;
            string connectionString = pubGetAppSettingsConn();
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    oracletrans = connection.BeginTransaction();

                    OracleParameter[] myparams;
                    foreach (DataRow dr in dt.Rows)
                    {
                        if (dr != null && dr.ItemArray.Length > 0)
                        {
                            myparams = assignparametervalues(dr);
                            OracleCommand command = BuildQueryCommand(connection, storedProcName, myparams);
                            command.Transaction = oracletrans;
                            command.ExecuteNonQuery();
                        }
                    }
                    OracleParameter[] myparams2;
                    foreach (DataRow dr2 in dt2.Rows)
                    {
                        if (dr2 != null && dr2.ItemArray.Length > 0)
                        {
                            myparams2 = assignparametervalues(dr2);
                            OracleCommand command = BuildQueryCommand(connection, storedProcName2, myparams2);
                            command.Transaction = oracletrans;
                            command.ExecuteNonQuery();
                        }
                    }
                }
                catch //(System.Data.SqlClient.SqlException e)
                {
                    oracletrans.Rollback();
                    connection.Close();
                    return false;
                }
                finally
                {
                    oracletrans.Dispose();
                    connection.Close();
                    connection.Dispose();
                }
            }

            return result;
        }
        public OracleParameter[] assignparametervalues(DataRow dr)
        {
            OracleParameter[] myparams = new OracleParameter[dr.Table.Columns.Count];
            for (int j = 0; j < dr.Table.Columns.Count; j++)
            {
                OracleParameter myparam = new OracleParameter();
                myparam.ParameterName = dr.Table.Columns[j].ColumnName.ToLower();
                myparam.Value = dr[dr.Table.Columns[j].ColumnName];
                myparams[j] = myparam;
            }
            return myparams;
        }
        public OracleDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
        {
            string connectionString = pubGetAppSettingsConn();
            OracleConnection connection = new OracleConnection(connectionString);
            OracleDataReader returnReader;
            connection.Open();
            OracleCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.CommandType = CommandType.StoredProcedure;
            returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
            return returnReader;

        }
        private OracleCommand BuildQueryCommand(OracleConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            OracleCommand command = new OracleCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            if (parameters != null)
            {
                foreach (OracleParameter parameter in parameters)
                {
                    if (parameter != null)
                    {
                        // 检查未分配值的输出参数,将其分配以DBNull.Value.
                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                            (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        command.Parameters.Add(parameter);
                    }
                }
            }

            return command;
        }


        #endregion

        #region 日志写入 
        public void proREG(string strMsgType, string strMsgMode, string strMsgInfo,
                           string strMaterialCode, string strMaterialName, string strSupplierCode, string strSupplierName)
        {
            proREG(strMsgType, strMsgMode, strMsgInfo,
                    strMaterialCode, strMaterialName, strSupplierCode, strSupplierName, null);
        }
        public void proREG(string strMsgType, string strMsgMode, string strMsgInfo,
                           string strMaterialCode, string strMaterialName, string strSupplierCode, string strSupplierName, string strBaseInfo)
        {
            SortedList SDList = new SortedList();
            SDList["V_MSGTYPE"] = strMsgType;
            SDList["V_MSGMODE"] = strMsgMode;
            SDList["V_MSGUSER"] = "";
            SDList["V_MSGINFO"] = strMsgInfo;
            SDList["V_MATERIALCODE"] = strMaterialCode;
            SDList["V_MATERIALNAME"] = strMaterialName;
            SDList["V_SUPPLIERCODE"] = strSupplierCode;
            SDList["V_SUPPLIERNAME"] = strSupplierName;
            //基地信息（基地名称/工厂信息）
            if (strBaseInfo == "2032") { strBaseInfo = "临海基地"; }
            else if (strBaseInfo == "D355") { strBaseInfo = "晋中基地"; }
            else if (strBaseInfo == "1081") { strBaseInfo = "春晓基地"; }
            else if (strBaseInfo== "1390,1062") { strBaseInfo = "北仑基地"; }
            SDList["V_BASEINFO"] = strBaseInfo;

            proSetDT(SDList, "PRO_SYS.MSGSAVE");
        }

        public void txtSaveToFile(string strText, string fileName, string strPath)
        {
            if (Directory.Exists(strPath) == false)
            {
                Directory.CreateDirectory(strPath);
            }
            strPath = strPath + fileName;
            txtSaveToFile(strText, strPath);
        }
        public void txtSaveErro(string strErro)
        {
            string strDateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            string strPATH = pubGetAppSettings("YT_SPS_IO");
            txtSaveToFile(strDateTime + "日志错误：" + strErro + "\r\n", strDateTime.Substring(0, 10) + ".txt",
                strPATH);
        }
        public void txtSaveErro(string strErro, string strPrefix)
        {
            if (string.IsNullOrEmpty(strPrefix))
            {
                strPrefix = "";
            }
            string strDateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            string strPATH = pubGetAppSettings("YT_SPS_IO");
            txtSaveToFile(strDateTime + "日志错误：" + strErro + "\r\n", strPrefix + strDateTime.Substring(0, 10) + ".txt",
                strPATH);
        }
        public void txtSaveToFile(string strText, string strPath)
        {
            FileStream fs = new FileStream(strPath, FileMode.OpenOrCreate, FileAccess.Write);
            StreamWriter m_streamWriter = new StreamWriter(fs);
            m_streamWriter.Flush();  // 使用StreamWriter来往文件中写入内容
            m_streamWriter.BaseStream.Seek(0, SeekOrigin.End);
            m_streamWriter.Write(strText);
            m_streamWriter.Close();
        }

        #endregion

        #region xml、DataSet相互

        public static DataSet ConvertXMLToDataSet(string xmlData)
        {
            XmlNodeReader reader = null;
            try
            {
                XmlDocument doc = new XmlDocument();
                doc.LoadXml(xmlData);
                reader = new XmlNodeReader(doc);
                DataSet xmlDS = new DataSet();
                xmlDS.ReadXml(reader);
                reader.Close();
                return xmlDS;
            }
            catch (Exception ex)
            {
                string tt = ex.ToString();
                return null;
            }
            finally
            {
                if (reader != null) reader.Close();
            }
        }
        public static DataSet ConvertXMLToDataSet2(string xmlData)
        {

            StringReader stream = null;
            XmlTextReader reader = null;
            try
            {
                DataSet xmlDS = new DataSet();
                stream = new StringReader(xmlData);
                //从stream装载到XmlTextReader
                reader = new XmlTextReader(stream);
                //用ReadXml读取，XmlReadMode 设置为ReadSchema读取数据同时读取框架
                xmlDS.ReadXml(reader, XmlReadMode.Auto);
                return xmlDS;
            }
            catch (Exception ex)
            {
                string tt = ex.ToString();
                return null;
            }
            finally
            {
                if (reader != null) reader.Close();
            }
        }
        public static string ConvertDataSetToXML2(DataSet xmlDS)
        {
            MemoryStream stream = null;
            XmlTextWriter writer = null;


            try
            {
                stream = new MemoryStream();
                //从stream装载到XmlTextReader
                writer = new XmlTextWriter(stream, Encoding.Unicode);
                //用WriteXml方法写入文件,XmlWriteMode设置为WriteSchema写入数据同时写入框架.
                xmlDS.WriteXml(writer);
                int count = (int)stream.Length;
                byte[] arr = new byte[count];
                stream.Seek(0, SeekOrigin.Begin);
                stream.Read(arr, 0, count);

                UnicodeEncoding utf = new UnicodeEncoding();
                return utf.GetString(arr).Trim();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (writer != null) writer.Close();
            }
        }

        #region 将DataTable转化为XML
        public static string ConvertDataTableToXML(DataTable xmlDS)
        {
            MemoryStream stream = null;
            XmlTextWriter writer = null;

            try
            {
                stream = new MemoryStream();
                //从stream装载到XmlTextReader
                writer = new XmlTextWriter(stream, Encoding.Unicode);
                //用WriteXml方法写入文件,XmlWriteMode设置为WriteSchema写入数据同时写入框架.
                xmlDS.WriteXml(writer, XmlWriteMode.WriteSchema);
                int count = (int)stream.Length;
                byte[] arr = new byte[count];
                stream.Seek(0, SeekOrigin.Begin);
                stream.Read(arr, 0, count);

                UnicodeEncoding utf = new UnicodeEncoding();
                return utf.GetString(arr).Trim();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (writer != null) writer.Close();
            }
        }
        #endregion
        #endregion

        #region 获取唯一的19位值
        /// <summary>
        /// 获取唯一的19位值
        /// </summary>
        /// <returns></returns>
        public string GenerateIntID()
        {
            byte[] buffer = Guid.NewGuid().ToByteArray();
            return BitConverter.ToInt64(buffer, 0).ToString();
        }
        #endregion

        #region 获取唯一的16位值
        /// <summary>
        /// 获取唯一的16位值
        /// </summary>
        /// <returns></returns>
        public string GenerateStringID()
        {
            long i = 1;
            foreach (byte b in Guid.NewGuid().ToByteArray())
            {
                i *= ((int)b + 1);
            }
            return string.Format("{0:x}", i - DateTime.Now.Ticks);
        }

        #endregion
    }
}
